package com.hb.trade.api.routine.util;



import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.fzzn.core.web.util.SpringUtils;

import java.io.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * describe:
 *
 * @author zhongdl
 * @date 2022/04/22
 */
@Slf4j
public class POIUtil {



    /**
     * @Author Zhongks
     * @Description //TODO 拷贝sheet（表）
     * @Date 17:16 2021/5/11
     * @Param [targetSheet, sourceSheet, targetWork, sourceWork, startRow, cellRangeAddressExcelVoList]
     * @return void
     **/
    public static void copySheet(Sheet targetSheet, Sheet sourceSheet,
                                 Workbook targetWork, Workbook sourceWork, int startRow) {
        if(targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null){
            throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时，targetSheet、sourceSheet、targetWork、sourceWork都不能为空，故抛出该异常！");
        }
        int lastRowNum = targetSheet.getLastRowNum() + 1;
        CellStyle cellStyle = targetSheet.getWorkbook().createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中

        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        //设置单元格默认宽度
//        targetSheet.setDefaultColumnWidth(25);
        //复制源表中的行
        int areadlyColunm = 0;
        for (int i = sourceSheet.getFirstRowNum(); i <= sourceSheet.getLastRowNum(); i++) {
            Row sourceRow = sourceSheet.getRow(i);
            if (sourceRow == null){
                sourceRow = sourceSheet.createRow(i);
            }
            short height = sourceRow.getHeight();
            Row targetRow = targetSheet.createRow(i+startRow);  //创建新的row
            if (sourceRow != null) {
                copyRow(targetRow, sourceRow,
                        targetWork, sourceWork,null);
            }
            //调整列宽(增量调整)
            if (sourceRow.getPhysicalNumberOfCells() > areadlyColunm) {
                for (int j = areadlyColunm; j < sourceRow.getPhysicalNumberOfCells(); j++) {
//                     targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
                }
                areadlyColunm = sourceRow.getPhysicalNumberOfCells();
            }



            targetRow.setHeight(height);
        }
        int startRowFlag = startRow == 0 ? 0 : 2;
        //复制样式
        int sheetMergerCount = sourceSheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress cellRangeAddress = sourceSheet.getMergedRegion(i);
            int firstRow = cellRangeAddress.getFirstRow();
            int lastRow = cellRangeAddress.getLastRow();
            cellRangeAddress.setFirstRow(firstRow + lastRowNum + startRowFlag);
            cellRangeAddress.setLastRow(lastRow+lastRowNum + startRowFlag);
            targetSheet.addMergedRegion(cellRangeAddress);


        }

        //自定义合并单元格样式（若不需要进行单元格合并操作，将cellRangeAddressExcelVoList赋值为null即可）
//        if(!CollectionUtils.isEmpty(cellRangeAddressExcelVoList)){
//            //合并单元格
//            for(CellRangeAddressExcelVo model:cellRangeAddressExcelVoList){
//                targetSheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(model.getFirstRow(),model.getLastRow(),model.getFirstCol(),model.getLastCol()));
//            }
//        }
    }


    /**
     * @Author Zhongks
     * @Description //TODO 拷贝row(行)
     * @Date 17:17 2021/5/11
     * @Param [targetRow, sourceRow, targetWork, sourceWork]
     * @return void
     **/
    public static void copyRow(Row targetRow, Row sourceRow,
                               Workbook targetWork, Workbook sourceWork, ExcelOperationUtil.StyleMapping mapping)  {
        if(targetRow == null || sourceRow == null || targetWork == null || sourceWork == null ){
            throw new IllegalArgumentException("调用PoiUtil.copyRow()方法时，targetRow、sourceRow、targetWork、sourceWork、targetPatriarch都不能为空，故抛出该异常！");
        }

        //设置行高
        targetRow.setHeight(sourceRow.getHeight());

        for (int i = sourceRow.getFirstCellNum(); i < sourceRow.getLastCellNum(); i++) {
            Cell sourceCell = sourceRow.getCell(i);
            Cell targetCell = null;

            if (sourceCell != null && getCellNull(sourceCell)) {
                if (targetCell == null) {
                    targetCell = targetRow.createCell(i);
                }
                //拷贝单元格，包括内容和样式
//                copyCell(targetCell, sourceCell, targetWork, sourceWork,mapping);
                copyCell(targetCell, sourceCell,true,true,mapping);
            }
        }

    }

    public static boolean getCellNull(Cell srcCell){
        boolean flag = false;
        switch (srcCell.getCellType()) {
            case STRING:
                flag = StrUtil.isNotEmpty(srcCell.getStringCellValue());
                break;
            case NUMERIC:
                flag = true;
                break;

            default:
                flag = true;
                break;
        }
        return flag;
    }

    /**
     * @Author Zhongks
     * @Description //TODO 拷贝cell（单元格）
     * @Date 17:18 2021/5/11
     * @Param [targetCell, sourceCell, targetWork, sourceWork]
     * @return void
     **/
    public static void copyCell(Cell targetCell, Cell sourceCell, Workbook targetWork, Workbook sourceWork) {
        if(targetCell == null || sourceCell == null || targetWork == null || sourceWork == null ){
            throw new IllegalArgumentException("调用PoiUtil.copyCell()方法时，targetCell、sourceCell、targetWork、sourceWork都不能为空，故抛出该异常！");
        }

        CellStyle targetCellStyle=targetWork.createCellStyle();
        targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle());//拷贝样式
        //重新添加样式（这里可以根据你的需要重新进行单元格样式添加）
        /*targetCellStyle.setBorderTop(BorderStyle.THIN);//设置上边框线
        targetCellStyle.setBorderLeft(BorderStyle.THIN);//设置左边框线
        targetCellStyle.setBorderBottom(BorderStyle.THIN);//设置下边框线
        targetCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框线*/
        targetCell.setCellStyle(targetCellStyle);

        targetCell.setCellValue(sourceCell.getStringCellValue());
    }

    /**
     * @Author Zhongks
     * @Description //TODO 拷贝cell（单元格）
     * @Date 17:18 2021/5/11
     * @Param [targetCell, sourceCell, targetWork, sourceWork]
     * @return void
     **/
    public static void copyCell(Cell targetCell, Cell sourceCell, Workbook targetWork, Workbook sourceWork,ExcelOperationUtil.StyleMapping mapping) {
        if(targetCell == null || sourceCell == null || targetWork == null || sourceWork == null ){
            throw new IllegalArgumentException("调用PoiUtil.copyCell()方法时，targetCell、sourceCell、targetWork、sourceWork都不能为空，故抛出该异常！");
        }
        Workbook srcBook = sourceCell.getSheet().getWorkbook();
        Workbook desBook = targetCell.getSheet().getWorkbook();

        CellStyle targetCellStyle=targetWork.createCellStyle();
        targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle());//拷贝样式
        //重新添加样式（这里可以根据你的需要重新进行单元格样式添加）
        /*targetCellStyle.setBorderTop(BorderStyle.THIN);//设置上边框线
        targetCellStyle.setBorderLeft(BorderStyle.THIN);//设置左边框线
        targetCellStyle.setBorderBottom(BorderStyle.THIN);//设置下边框线
        targetCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框线*/
        targetCell.setCellStyle(targetCellStyle);

        targetCell.setCellValue(sourceCell.getStringCellValue());
        try {
            if (null != mapping) {
                short desIndex = mapping.desIndex(sourceCell.getCellStyle().getIndex());
                targetCell.setCellStyle(srcBook.getCellStyleAt(desIndex));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    public static void copyCell(Cell desCell, Cell srcCell, boolean copyValueFlag, boolean copyStyleFlag, ExcelOperationUtil.StyleMapping mapping) {
        try {
            Workbook srcBook = srcCell.getSheet().getWorkbook();
            Workbook desBook = desCell.getSheet().getWorkbook();

            //复制样式
            //如果是同一个excel文件内，连带样式一起复制
            if (srcBook == desBook && copyStyleFlag) {
                //同文件，复制引用
                desCell.setCellStyle(srcCell.getCellStyle());
            } else if (copyStyleFlag) {
                //不同文件，通过映射关系复制
                if (null != mapping) {
                    short desIndex = mapping.desIndex(srcCell.getCellStyle().getIndex());
                    desCell.setCellStyle(desBook.getCellStyleAt(desIndex));
                }
            }

            //复制评论
            if (srcCell.getCellComment() != null) {
                desCell.setCellComment(srcCell.getCellComment());
            }

            //复制内容
            desCell.setCellType(srcCell.getCellType());

            if (copyValueFlag) {
                switch (srcCell.getCellType()) {
                    case STRING:
                        desCell.setCellValue(srcCell.getStringCellValue());
                        break;
                    case NUMERIC:
                        desCell.setCellValue(srcCell.getNumericCellValue());
                        break;
                    case FORMULA:
                        desCell.setCellFormula(srcCell.getCellFormula());
                        break;
                    case BOOLEAN:
                        desCell.setCellValue(srcCell.getBooleanCellValue());
                        break;
                    case ERROR:
                        desCell.setCellValue(srcCell.getErrorCellValue());
                        break;
                    case BLANK:
                        //nothing to do
                        break;
                    default:
                        break;
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }




    public static File mergeWorkSheet(File sourceFile,String sheetName,String filePath,int sheetSize){
        InputStream in = null;
        HSSFWorkbook fromExcel = null;
        FileOutputStream fileOut = null;
        try(Workbook targetWorkbook = new HSSFWorkbook()){
            HSSFWorkbook sourceWorkbook = new HSSFWorkbook(new FileInputStream(sourceFile));
            int startRow = 0;
            Sheet targetSheet = targetWorkbook.createSheet(sheetName);

            for (int i = 0; i < sheetSize; i++) {
                Sheet sheet = sourceWorkbook.getSheetAt(i);
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
//                copySheet(targetSheet, sheet, targetWorkbook, sourceWorkbook,startRow,ExcelOperationUtil.copyCellStyle(sourceWorkbook,targetWorkbook));
                int lastRowNum = sheet.getLastRowNum();
                for (int j = 0; j < lastRowNum; j++) {
                    sheet.setColumnWidth(j,sheet.getColumnWidth(j));
                }
                startRow += physicalNumberOfRows;
            }
            //复制列宽

            String allFileName = filePath + File.separator+sheetName + ".xlsx";    //定义新生成的xlx表格文件
            fileOut = new FileOutputStream(allFileName);
            targetWorkbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
            File file = new File(allFileName);
            return file;
        }catch (Exception e){
            log.error("Workbook合并出错",e);
            return null;
        }
    }



    public static File mergeWorkSheet(File sourceFile,String fileName,String sheetName,String filePath,int sheetSize,int tmplateIndex){
        InputStream in = null;
        HSSFWorkbook fromExcel = null;
        FileOutputStream fileOut = null;
        try(Workbook targetWorkbook = new HSSFWorkbook()){
//            File absoluteFile = new File(SpringUtils.getLocalFile("template").getAbsoluteFile() + File.separator + "pingbiaobaogaomoban.xlsx");
//            File absoluteFile = new File(SpringUtils.getLocalFile("template").getAbsoluteFile() + File.separator + "pingbiaobaogaomoban.xlsx");

            InputStream stream = SpringUtils.getLocalStream("template/pingbiaobaogaomoban.xlsx");
            XSSFWorkbook templateWorkbook = new XSSFWorkbook(stream);
            XSSFSheet sheetAt = templateWorkbook.getSheetAt(tmplateIndex);


            HSSFWorkbook sourceWorkbook = new HSSFWorkbook(new FileInputStream(sourceFile));
            int startRow = 0;
            Sheet targetSheet = targetWorkbook.createSheet(sheetName);
            for (int i = 0; i < 22; i++) {
                targetSheet.setColumnWidth(i,sheetAt.getColumnWidth(i));
            }
            for (int i = 0; i < sheetSize; i++) {
                Sheet sheet = sourceWorkbook.getSheetAt(i);
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                copySheet(targetSheet, sheet, targetWorkbook, sourceWorkbook,startRow);
                int lastRowNum = sheet.getLastRowNum();

                startRow += (physicalNumberOfRows + 2);
            }
            //复制列宽

            String allFileName = filePath + File.separator+fileName + ".xlsx";    //定义新生成的xlx表格文件
            fileOut = new FileOutputStream(allFileName);
            targetWorkbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
            File file = new File(allFileName);
            return file;
        }catch (Exception e){
            log.error("Workbook合并出错",e);
            return null;
        }
    }


//       public static void hebign(){
//
//           //将待合并Excel文档的名称保存至字符串数组
//           String[] inputFiles = new String[]{
//                   "E:/demo/file1.xlsx",
//                   "E:/demo/file2.xlsx"};
//
//           //创建一个新的Excel文档
//           Workbook newBook = new Workbook();
//
//           //清除默认的工作表
//           newBook.getWorksheets().clear();
//
//           //创建一个临时的Workbook对象
//           Workbook tempBook = new Workbook();
//
//           //遍历数组
//           for (String file : inputFiles)
//           {
//               //依次加载每个Excel文档
//               tempBook.loadFromFile(file,ExcelVersion.Version2013);
//
//               //将文档中的所有工作表复制到新建的Excel文档中
//               for (Object obj : tempBook.getWorksheets())
//               {
//                   Worksheet sheet= (Worksheet) obj;
//                   newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
//               }
//           }
//
//           //保存新文档到指定路径
//           newBook.saveToFile("MergeFiles.xlsx", ExcelVersion.Version2013);
//       }


       public static void read() throws IOException {
           List<Row> rowList = new ArrayList<>();
           //获取工作簿
           Workbook workbook = new XSSFWorkbook("E:\\demo\\file1.xlsx");
           //获取工作表
           Sheet sheet = workbook.getSheetAt(0);
           //获取表中的最后一行的索引
           int lastRowNum = sheet.getLastRowNum();
           //获取单元格列数
           Row row1 = sheet.getRow(0);
           int lastCellNum = row1.getLastCellNum();
           XSSFCell cell = null;

           //遍历表中行
           for (int i = 0; i <= lastRowNum; i++) {
               Row sheetRow = sheet.getRow(i);
               rowList.add(sheetRow);
           }
           //关闭对象
           workbook.close();

           Workbook workbook1 = new XSSFWorkbook();
           Sheet sheet1 = workbook1.createSheet();
           for (int i = 0; i <= lastRowNum; i++) {
               Row row = sheet1.createRow(i);
               Row cells = rowList.get(i);
               int j = 0;
               for (Cell cell1 : cells) {
//                   row.createCell(j).setCellValue(cell1.getValue);
               }

           }
       }





}
